Mortgage Affordability Analysis

#Convert this notebook to html using pretty-jupyter
#This cell won't show up due to cell-level metadata
# Uncomment the line below and run this cell to produce the HTML report
!jupyter nbconvert --to html --template pj house_buying.ipynb

import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
import matplotlib.colors as mcolors
import streamlit as st
import plotly.express as px

# Loan and Property Parameters
purchase_price = 285_000        # Purchase price of the property
down_payment = 30_000           # Down payment amount
note_rate = 0.06                # Annual interest rate on mortgage note
term_years = 30                 # Term of mortgage in years
effective_tax_rate = 0.0091     # Effective property tax rate (0.91%)
annual_premium = 3_120          # Annual insurance premium (I'm seeing avg. costs of $3,069 - $3,732 annually)
pmi_annual_rate = 0.005         # Annual PMI rate (0.5% of loan amount) (I'm seeing avg. rates range from 0.3% - 1.5%)
hoa_monthly = 0                 # Monthly HOA fees 
flood_annual_premium = 400      # Annual flood insurance premium (Seeing costs of $300 - $1,200 annually for FEMA Zone X)

# Additional Monthly Expenses
utilities_monthly = 243         # Monthly utilities estimates for the midwest; $129 (electricity) + $43 (water/sewer) + $71 (gas)
internet_monthly = 50           # Monthly internet estimate - 1Gbps TDS Inc. internet plan (https://tdstelecom.com/shop/packages/best-deals.html)
maintenance_monthly = purchase_price * 0.01 / 12  # Monthly maintenance rate (1% of purchase price)

def monthly_payment_with_utilities(purchase_price, down_payment, note_rate, term_years, effective_tax_rate,
                    annual_premium, pmi_annual_rate, hoa_monthly, verbose=False):
    """Calcuates the total estimated monthly payment for a house including utilities and maintenance.

    Args:
        purchase_price (float): The total price of the house.
        down_payment (float): The down payment made on the house.
        note_rate (float): The annual interest rate on the mortgage.
        term_years (integer): The term of the mortgage in years.
        effective_tax_rate (float): The effective property tax rate.
        annual_premium (integer): The annual homeowners insurance premium.
        pmi_annual_rate (float): The annual mortgage insurance premium rate.
        hoa_monthly (integer): The monthly homeowners association fee.
        verbose (bool, optional): If True, prints detailed payment breakdown. Defaults to False.

    Returns:
        float: The total estimated monthly payment.
    """    
    # <1> Principal & Interest (P&I)
    L_base = purchase_price - down_payment                  # Base loan amount
    i = note_rate / 12                                      # Monthly interest rate
    n = term_years * 12                                     # Total number of payments (e.g. 30 years)
    P_and_I = L_base * (i * (1 + i)**n) / ((1 + i)**n - 1)  # Amortizing loan formula (https://www.investopedia.com/terms/a/amortization.asp)

    # <2> Property Taxes
    taxes_monthly = (purchase_price * effective_tax_rate) / 12  # Monthly property taxes; assumes purchase price equals assessed value

    # <3> Homeowners Insurance
    hoi_monthly = annual_premium / 12                           # Estimated monthly homeowners insurance

    # <4> Mortgage Insurance (PMI) - Will be included if down payment < 20% 
    if down_payment < 0.20 * purchase_price:
        pmi_monthly = (L_base * pmi_annual_rate) / 12
    else:
        pmi_monthly = 0

    # <5> Flood Insurance
    flood_monthly = flood_annual_premium / 12  # Estimated monthly flood insurance

    # Total
    total_monthly_payment = P_and_I + taxes_monthly + hoi_monthly + pmi_monthly + hoa_monthly + flood_monthly + utilities_monthly + internet_monthly + maintenance_monthly

    if verbose:
        print(f"Total Estimated Monthly Payment (including utilities & maintenance): ${total_monthly_payment:,.2f}")
        print("=============================================")
        print(f"Principal & Interest (P&I): ${P_and_I:,.2f}")
        print(f"Property Taxes: ${taxes_monthly:,.2f}")
        print(f"Homeowners Insurance: ${hoi_monthly:,.2f}")
        print(f"Mortgage Insurance (PMI): ${pmi_monthly:,.2f}")
        print(f"Flood Insurance: ${flood_monthly:,.2f}")
        print(f"Utilities & Maintenance: ${utilities_monthly + internet_monthly + maintenance_monthly:,.2f}")

    return round(total_monthly_payment, 2)


def monthly_payment(purchase_price, down_payment, note_rate, term_years, effective_tax_rate,
                    annual_premium, pmi_annual_rate, hoa_monthly, verbose=False):
    """Calcuates the total estimated monthly payment for a house; does not include utilities and maintenance.

    Args:
        purchase_price (float): The total price of the house.
        down_payment (float): The down payment made on the house.
        note_rate (float): The annual interest rate on the mortgage.
        term_years (integer): The term of the mortgage in years.
        effective_tax_rate (float): The effective property tax rate.
        annual_premium (integer): The annual homeowners insurance premium.
        pmi_annual_rate (float): The annual mortgage insurance premium rate.
        hoa_monthly (integer): The monthly homeowners association fee.
        verbose (bool, optional): If True, prints detailed payment breakdown. Defaults to False.

    Returns:
        float: The total estimated monthly payment.
    """    
    
    # <1> Principal & Interest (P&I)
    L_base = purchase_price - down_payment                  
    i = note_rate / 12                                      # Monthly interest rate
    n = term_years * 12                                     # Total number of payments (e.g. 30 years)
    P_and_I = L_base * (i * (1 + i)**n) / ((1 + i)**n - 1)  # Monthly P&I payment (Amortizing loan formula)

    # <2> Property Taxes
    taxes_monthly = (purchase_price * effective_tax_rate) / 12  # Monthly property taxes; assumes purchase price equals assessed value

    # <3> Homeowners Insurance
    hoi_monthly = annual_premium / 12                           # Estimated monthly homeowners insurance

    # <4> Mortgage Insurance (PMI) - Will be included if down payment < 20% (https://crosscountrymortgage.com/mortgage/resources/how-to-calculate-pmi/)
    if down_payment < 0.20 * purchase_price:
        pmi_monthly = (L_base * pmi_annual_rate) / 12           # Estimated monthly PMI
    else:
        pmi_monthly = 0

    # <5> Flood Insurance
    flood_monthly = flood_annual_premium / 12                   # Estimated monthly flood insurance

    # Total
    total_monthly_payment = P_and_I + taxes_monthly + hoi_monthly + pmi_monthly + hoa_monthly + flood_monthly

    if verbose:
        print(f"Total Estimated Monthly Payment (w/out utilities & maintenance): ${total_monthly_payment:,.2f}")
        print("=============================================")
        print(f"Principal & Interest (P&I): ${P_and_I:,.2f}")
        print(f"Property Taxes: ${taxes_monthly:,.2f}")
        print(f"Homeowners Insurance: ${hoi_monthly:,.2f}")
        print(f"Mortgage Insurance (PMI): ${pmi_monthly:,.2f}")
        print(f"Flood Insurance: ${flood_monthly:,.2f}")

    return round(total_monthly_payment, 2)

monthly_payment_with_utilities(purchase_price=purchase_price,
                down_payment=down_payment, note_rate=note_rate, term_years=term_years,
                effective_tax_rate=effective_tax_rate, annual_premium=annual_premium,
                pmi_annual_rate=pmi_annual_rate, hoa_monthly=hoa_monthly, verbose=True)

monthly_payment(purchase_price=purchase_price,
                down_payment=down_payment, note_rate=note_rate, term_years=term_years,
                effective_tax_rate=effective_tax_rate, annual_premium=annual_premium,
                pmi_annual_rate=pmi_annual_rate, hoa_monthly=hoa_monthly, verbose=True)

prices = np.round(np.arange(230_000, 300_001, 7_000), 3)    # 230k to 310k (inclusive) in 7k increments
rates = np.round(np.arange(0.050, 0.0751, 0.005), 3)        # 5.0% to 7.5% (inclusive) in 0.5% increments   

df = pd.DataFrame(index=rates, columns=prices, dtype=float) # Create empty DataFrame to hold results
df.index.name = 'interest_rate'                             # Rows = interest rates
df.columns.name = 'purchase_price'                          # Columns = purchase prices

for r in rates:         # For each interest rate (row)
    for p in prices:    # For each purchase price (column)
        payment = monthly_payment(purchase_price=p, down_payment=down_payment, note_rate=r,         # Calculate monthly payment
                                  term_years=term_years, effective_tax_rate=effective_tax_rate, 
                                  annual_premium=annual_premium, pmi_annual_rate=pmi_annual_rate,
                                  hoa_monthly=hoa_monthly, verbose=False)
        df.loc[r, p] = round(payment, 2)                                                            # Store result in DataFrame

# Create a display-friendly version of the DataFrame
df_display = df.copy()
df_display.index = (df_display.index * 100).map('{:.2f}%'.format)   # Format interest rates as percentages
df_display.index.name = 'Interest Rate'                               # Rename index for display
df_display.columns.name = 'Purchase Price'                          # Rename columns for display
df_display.columns = df_display.columns.map('${:,.0f}'.format)      # Format purchase prices as currency
df_display = df_display.style.format('${:,.2f}')                    # Format all values as currency with 2 decimal places
df_display

labels = df.map(lambda v: f"${v:,.2f}")                 # Format monthly payment labels for each heatmap cell

plt.figure(figsize=(12,10))
ax = sns.heatmap(df,                                    # The heatmap is an ax object
                 cmap='RdYlGn_r',                       # Color map (red-yellow-green reversed)
                 linewidths=0.5,                        # Width of lines that will divide each cell
                 cbar_kws={'label':'Monthly Payment'},  # Color bar label
                 annot=labels,                          # Annotate each cell with the formatted monthly payment
                 fmt="",                                # No special string formatting needed since labels are pre-formatted
                 vmin=1_700, vmax=2_025)                # Anchor the color scale between these two values

# Axis labels
ax.set_xlabel('Purchase Price')
ax.set_ylabel('Interest Rate')

# Format tick labels
ax.set_xticklabels([f"${c:,.0f}" for c in df.columns], rotation=45, ha="right") # Format/rotate x-axis labels for better readability
ax.set_yticklabels([f"{r:.1%}" for r in df.index], rotation=0)                  # Format y-axis labels as percentages

cbar = ax.collections[0].colorbar                       # Grab the color bar object from the ax heatmap
cbar.formatter = mtick.StrMethodFormatter("${x:,.0f}")  # Format color bar ticks as currency
cbar.update_ticks()                                     # Update color bar ticks to reflect new formatting

plt.title('Monthly Payment\nby Purchase Price & Interest Rate')
plt.tight_layout()
plt.show()